﻿
/****** Object:  Stored Procedure dbo.GenLista    Script Date: 30.05.2006 7:48:46 AM ******/

/****** Object:  Stored Procedure dbo.GenLista    Script Date: 22/02/2006 5:44:41 PM ******/

/****** Object:  Stored Procedure dbo.GenLista    Script Date: 2/1/2006 7:51:54 AM ******/
CREATE PROCEDURE [dbo].[GenLista]   @idLista smallint AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @dataEndFunctie varchar(50)
DECLARE @lunaCurenta smallint
DECLARE @ultimaData datetime

--determina ultima data la care s-a lucrat
SELECT @ultimaData= MAX(DataDoc) FROM RulajeDoc


SET @categorie = 0
SET @strcategorie = '0'
SET @analitica = 0
SET @cfm = 0
SET @lunaCurenta=1

SELECT @strDataStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLD' AND [User]=suser_sname() AND [Host]=host_name()
SET @strDataStart = LTRIM(@strDataStart)
SET @strDataStart = RTRIM(@strDataStart)
SET @dataStartFunctie = 'NULL'
IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataStartUI = CAST(CAST(@strDataStart AS float)-2  AS datetime)
	SET @strDataStart = ''''+CAST(@dtDataStartUI AS varchar(50))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@dtDataStartUI) as varchar(4))+'-'+CAST(MONTH(@dtDataStartUI) AS varchar(2))+'-'+CAST(DAY(@dtDataStartUI) AS varchar(2))+''''
END

SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLD' AND [User]=suser_sname() AND [Host]=host_name()
SET @strDataEnd = LTRIM(@strDataEnd)
SET @strDataEnd = RTRIM(@strDataEnd)
SET @dataEndFunctie = 'NULL'
IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
	SET @dataEndFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-'+CAST(DAY(@dtDataEndUI) AS varchar(2))+''''
	SET @lunaCurenta = MONTH(@dtDataEndUI)	
END
DECLARE @tipb as varchar(100)
IF @idLista = 1
BEGIN
	
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TPB' AND [User]=suser_sname() AND [Host]=host_name()
	IF @tipb='Extra Bilantiera'
	BEGIN
		SET @strcategorie = '2'			
	END
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='ANL' AND [User]=suser_sname() AND [Host]=host_name()
	if @tipb='1'
		SET @analitica =1
	--conturi fara miscari
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CFM' AND [User]=suser_sname() AND [Host]=host_name()
	if @tipb='1'
		SET @cfm =1


	SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='LDT' AND [User]=suser_sname() AND [Host]=host_name()
	SET @strDataEnd = LTRIM(@strDataEnd)
	SET @strDataEnd = RTRIM(@strDataEnd)
	
	IF LEN(@strDataEnd)<>0
	BEGIN
		SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
		SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
		SET @dataEndFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-'+CAST(DAY(@dtDataEndUI) AS varchar(2))+''''
		SET @lunaCurenta = MONTH(@dtDataEndUI)	
		SET @dataStartFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-01'''
	END
	ELSE
	BEGIN
		SET @dataEndFunctie = ''''+CAST(@ultimaData AS varchar(50))+''''
		SET @dataStartFunctie = ''''+CAST(YEAR(@ultimaData) as varchar(4))+'-'+CAST(MONTH(@ultimaData) AS varchar(2))+'-01'''
		SET @lunaCurenta = MONTH(@ultimaData)	
	END
END

IF @idLista = 2
BEGIN
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='ANL' AND [User]=suser_sname() AND [Host]=host_name()
	if @tipb='1'
		SET @analitica =1
	PRINT 'Lista Fisa cont ' + cast(@analitica as varchar(2))
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TPB' AND [User]=suser_sname() AND [Host]=host_name()
	IF @tipb='Extra Bilantiera'
	BEGIN
		SET @strcategorie = '2'			
	END
END


SELECT @strContStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name()
SELECT @strContEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name()


IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

CREATE TABLE [#balanta_t] (
	[Cont] varchar(50) NOT NULL, 
	[Denumire] varchar(200) NOT NULL DEFAULT '',
	[Tip] varchar(3) NOT NULL DEFAULT '',
	[ContParinte] varchar(50) NOT NULL, 
	[Terminal] bit NOT NULL,	

	[DebInit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreInit] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DebPrec] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrePrec] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DebPrecCalc] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrePrecCalc] DECIMAL(20,2) NOT NULL DEFAULT 0,


	[DebCurent] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreCurent] DECIMAL(20,2) NOT NULL DEFAULT 0,
)

DECLARE @strSelect	varchar(8000)
DECLARE @filtData varchar(500)
DECLARE @filtDataC varchar(500)
DECLARE @filtC varchar(500)
SET @filtData = ''
SET @filtDataC = ''
SET @filtC = ''

IF LEN(@strContStart) <>0
	SET @filtC =  '  AND C.Cont >='''+@strContStart + ''' '

IF LEN(@strContEnd) <>0
	SET @filtC = @filtC + ' AND C.Cont <='''+@strContEnd + ''' '

IF @strContStart = @strContEnd
	SET @filtC = ' AND C.Cont like '''+@strContEnd + '%'' '

SET @filtData = @filtC
SET @filtData = REPLACE(@filtData,'C.Cont','F.ContDebit') 
IF @idLista = 2
	SET @filtData = @filtData + ' OR F.ContDebit LIKE '''+@strContEnd+'%'''

SET @filtDataC = REPLACE(@filtData,'F.ContDebit','F.ContCredit') 



SET @strSelect =' INSERT INTO [#balanta_t]
	 SELECT c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit], 
	DebitPrecedent, CreditPrecedent,sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc,  
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
	FROM [Conturi]  c
LEFT OUTER JOIN 
	(SELECT  
			COALESCE(t2.ContCredit, t1.ContDebit) Cont,
			COALESCE(t1.SumaDebitPrec , 0) DebPrecCalc, 
			COALESCE(t2.SumaCreditPrec , 0) CrePrecCalc,  
			COALESCE(t1.SumaDebit , 0) DebCurent, 
			COALESCE(t2.SumaCredit , 0) CreCurent 
		FROM 
			(SELECT coalesce(SUM(dbo.InainteDeData(F.ValoareLei,cast('+@dataStartFunctie+' as datetime),D.DataDoc)),0) sumaDebitPrec,
			coalesce(SUM(dbo.IntreDatele(F.ValoareLei,cast('+@dataStartFunctie+' as datetime),cast('+@dataEndFunctie+' as datetime),D.DataDoc)),0) sumaDebit, F.ContDebit 
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContDebit<>''N/A''  '  + @filtData + 
	'GROUP BY F.ContDebit) t1 
		FULL OUTER JOIN 
			(SELECT coalesce(SUM(dbo.InainteDeData(F.ValoareLei,cast('+@dataStartFunctie+' as datetime),D.DataDoc)),0) sumaCreditPrec,
			coalesce(SUM(dbo.IntreDatele(F.ValoareLei,cast('+@dataStartFunctie+' as datetime), cast('+@dataEndFunctie+' as datetime),D.DataDoc)),0) sumaCredit, F.ContCredit
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContCredit <>''N/A'' ' + @filtDataC + '
			GROUP BY F.ContCredit) t2 
		ON t1.ContDebit = t2.ContCredit 
	) RUL 
ON dbo.IsContInclus(c.cont,rul.cont)=1'

SET @strSelect = @strSelect + ' WHERE  c.[Cont]<>''N/A''  ' + @filtC+' AND Categorie='+@strcategorie
if @analitica=0
	SET @strSelect = @strSelect +' and (CHARINDEX(''.'',c.[Cont])=0 OR (CHARINDEX(''7701.'',c.[Cont])=1 AND LEN(RTRIM(LTRIM(c.cont)))=7)) '	
if @cfm=0
	SET @strSelect = @strSelect +' and (DebCurent <> 0 OR DebitInitial <> 0 OR DebitPrecedent <> 0 OR CreCurent <> 0 OR CreditInitial <> 0 OR CreditPrecedent <> 0  OR DebPrecCalc<>0 OR CrePrecCalc<>0)' 

SET @strSelect = @strSelect + 'GROUP BY  c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent
ORDER BY c.Cont '

--ON substring(rul.cont,1,len(c.cont))=c.cont


PRINT @strSelect
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				



if @analitica=1 AND @idLista = 2
BEGIN
SELECT  Cont,ContParinte,Terminal,Denumire,Tip,DebInit,CreInit, DebPrec+DebPrecCalc DebPrec,CrePrec+CrePrecCalc CrePrec, DebCurent, CreCurent,
	dbo.SoldDebit(Tip, DebInit, CreInit) SoldInitDeb, 
	dbo.SoldCredit(Tip, DebInit, CreInit) SoldInitCre, 
	dbo.Sold(Tip, DebInit, CreInit) SoldInit, 
	dbo.SoldDebit(Tip, DebPrec+DebPrecCalc, CrePrec+CrePrecCalc) SoldPrecDeb, 
	dbo.SoldCredit(Tip, DebPrec+DebPrecCalc, CrePrec+CrePrecCalc) SoldPrecCre, 
	dbo.Sold(Tip, DebPrec, CrePrec) SoldPrec, 
	dbo.SoldDebit(Tip, DebCurent, CreCurent) SoldCurentDeb, 
	dbo.SoldCredit(Tip, DebCurent, CreCurent) SoldCurentCre, 
	dbo.Sold(Tip, DebCurent, CreCurent) SoldCurent,
	DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0) DebTotal, CreInit + CrePrec + CrePrecCalc + COALESCE(CreCurent, 0) CreTotal, 
	dbo.SoldDebit(Tip, DebInit + DebPrec +DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalCre,
	dbo.Sold(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotal
INTO [#balanta1] FROM [#balanta_t]  WHERE [Terminal]=1 	
END
ELSE
BEGIN
SELECT  Cont,ContParinte,Terminal,Denumire,Tip,DebInit,CreInit, DebPrec+DebPrecCalc DebPrec,CrePrec+CrePrecCalc CrePrec, DebCurent, CreCurent,
	dbo.SoldDebit(Tip, DebInit, CreInit) SoldInitDeb, 
	dbo.SoldCredit(Tip, DebInit, CreInit) SoldInitCre, 
	dbo.Sold(Tip, DebInit, CreInit) SoldInit, 
	dbo.SoldDebit(Tip, DebPrec+DebPrecCalc, CrePrec+CrePrecCalc) SoldPrecDeb, 
	dbo.SoldCredit(Tip, DebPrec+DebPrecCalc, CrePrec+CrePrecCalc) SoldPrecCre, 
	dbo.Sold(Tip, DebPrec, CrePrec) SoldPrec, 
	dbo.SoldDebit(Tip, DebCurent, CreCurent) SoldCurentDeb, 
	dbo.SoldCredit(Tip, DebCurent, CreCurent) SoldCurentCre, 
	dbo.Sold(Tip, DebCurent, CreCurent) SoldCurent,
	DebInit + DebPrec +DebPrecCalc+ COALESCE(DebCurent, 0) DebTotal, CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0) CreTotal, 
	dbo.SoldDebit(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalCre,
	dbo.Sold(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotal
INTO [#balanta] FROM [#balanta_t] 	
END

SET @strSelect ='INSERT INTO'
if @analitica=1 AND @idLista = 2
	SET @strSelect = @strSelect + ' [#balanta1] '
else	
	SET @strSelect = @strSelect + ' [#balanta] '
SET @strSelect = @strSelect +'
SELECT ''0'',''0'',0,''TOTAL'', ''X'', 
	COALESCE(SUM(DebInit), 0), COALESCE(SUM(CreInit), 0),
	COALESCE(SUM(DebPrec), 0), COALESCE(SUM(CrePrec), 0),
	COALESCE(SUM(DebCurent), 0), COALESCE(SUM(CreCurent), 0),
	COALESCE(SUM(SoldInitDeb), 0), COALESCE(SUM(SoldInitCre), 0),
	COALESCE(SUM(SoldInit), 0),
	COALESCE(SUM(SoldPrecDeb), 0), COALESCE(SUM(SoldPrecCre), 0),
	COALESCE(SUM(SoldPrec), 0),
	COALESCE(SUM(SoldCurentDeb), 0), COALESCE(SUM(SoldCurentCre), 0),
	COALESCE(SUM(SoldCurent), 0),
	COALESCE(SUM(DebTotal), 0), COALESCE(SUM(CreTotal), 0),
	COALESCE(SUM(SoldTotalDeb), 0), COALESCE(SUM(SoldTotalCre), 0),
	COALESCE(SUM(SoldTotal), 0)'
if @analitica=1 AND @idLista = 2
	SET @strSelect = @strSelect + ' FROM [#balanta1] '
else	
	SET @strSelect = @strSelect + ' FROM [#balanta] '

--IF @analitica=0
--	SET @strSelect=@strSelect + 'WHERE ContParinte=''N/A'''
--ELSE
--	SET @strSelect=@strSelect + 'WHERE COALESCE(CHARINDEX(''.'',[Cont]),0)=0'
--SET @strSelect=@strSelect + 'WHERE terminal=1'

SET @strSelect=@strSelect + 'WHERE ContParinte=''N/A'''
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE	
if @analitica=1 AND @idLista = 2
SELECT Cont,ContParinte,Denumire,Terminal,Tip,DebInit,CreInit, DebPrec,CrePrec, DebCurent,CreCurent, DebPrec+DebCurent as [RulDebTotal], CrePrec+CreCurent as [RulCreTotal],
DebInit+DebPrec+DebCurent as [DebTotal], CreInit + CrePrec+CreCurent as [CreTotal], SoldTotalDeb, SoldTotalCre,SoldInit, SoldPrec, SoldCurent,SoldTotal FROM [#balanta1] 
ORDER BY Cont
else
SELECT Cont,ContParinte,Denumire,Terminal,Tip,DebInit,CreInit, DebPrec,CrePrec, DebCurent,CreCurent, DebPrec+DebCurent as [RulDebTotal], CrePrec+CreCurent as [RulCreTotal],
DebInit+DebPrec+DebCurent as [DebTotal], CreInit + CrePrec+CreCurent as [CreTotal], SoldTotalDeb, SoldTotalCre,SoldInit, SoldPrec, SoldCurent,SoldTotal FROM [#balanta] 
ORDER BY Cont


GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
Gata: